﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;

namespace 连接数据库查询
{
    class MsSql
    {
        private SqlConnection sqlConn;
        public MsSql()
        {
            sqlConn = new SqlConnection();
        }
        private bool LinkDbSrv()
        {
            sqlConn.Close();
            sqlConn.ConnectionString = "server=(local)\\sqlexpress;database=xscj_db;uid=xscjgl_sa;pwd=abcd@1234;";
            try
            {
                sqlConn.Open();
                return sqlConn.State == System.Data.ConnectionState.Open;
            }
            catch (Exception)
            {
                return false;
            }
        }
        public bool LocateData(string xh,out string xm,out string xb,out string nj,out string xy,out string zy,out string bj,out string dh,out string sError )
        {
            xm = "";
            xb = "";
            nj = "";
            xy = "";
            zy = "";
            bj = "";
            dh = "";
            sError = "";

            if (!LinkDbSrv())
            {
                sError = "数据库连接失败!";
                return false;
            }

            SqlCommand cmd = sqlConn.CreateCommand();
            cmd.CommandText =string.Format("select * from Table_Xsxx where 学号='{0}'",xh);
            cmd.CommandType = System.Data.CommandType.Text;

            try
            {
                SqlDataReader sdr = cmd.ExecuteReader();
                while (sdr.Read())
                {
                    xm = sdr["姓名"].ToString().Trim();
                    xb= sdr["性别"].ToString().Trim();
                    nj = sdr["年级"].ToString().Trim();
                    xy = sdr["学院"].ToString().Trim();
                    zy = sdr["专业"].ToString().Trim();
                    bj = sdr["班级"].ToString().Trim();
                    dh = sdr["电话"].ToString().Trim();
                }
                sdr.Close();
                if (xm != "")
                    sError = "";
                else
                    sError = "学号为【" + xh + "】的学生不存在！";
                return (xm != "");
            }
            catch (Exception ex)
            {
                return false;
                sError = ex.Message;
            }
        }


        public int InsertData(string xh, string xm, string xb, string nj, string xy, string zy, string bj, string dh, out string sError)
        {
            sError = "";
            if (!LinkDbSrv())
            {
                sError = "数据库连接失败！";
                return 0;
            }

            SqlCommand cmd = sqlConn.CreateCommand();
            cmd.CommandText = string.Format("insert into Table_Xsxx (学号,姓名,性别,年级,学院,专业,班级,电话) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')", xh, xm, xb, nj, xy, zy, bj, dh);
            cmd.CommandType = System.Data.CommandType.Text;

            try
            {
                int iResult = cmd.ExecuteNonQuery();
                sError = "";
                return iResult;
            }
            catch (Exception ex)
            {
                sError = ex.Message;
                return 0;
                //throw;
            }
        }

        public int UpdateData(string xh, string xm, string xb, string nj, string xy, string zy, string bj, string dh, out string sError)
        {
            sError = "";
            if (!LinkDbSrv())
            {
                sError = "数据库连接失败！";
                return 0;
            }

            SqlCommand cmd = sqlConn.CreateCommand();
            cmd.CommandText = string.Format("update Table_Xsxx set 姓名='{1}',性别='{2}',年级='{3}',学院='{4}',专业='{5}',班级='{6}',电话='{7}' where 学号='{0}'", xh, xm, xb, nj, xy, zy, bj, dh);
            cmd.CommandType = System.Data.CommandType.Text;

            try
            {
                int iResult = cmd.ExecuteNonQuery();
                sError = "";
                return iResult;
            }
            catch (Exception ex)
            {
                sError = ex.Message;
                return 0;
                //throw;
            }
        }

        public int DeleteData(string xh, out string sError)
        {
            sError = "";
            if (!LinkDbSrv())
            {
                sError = "数据库连接失败！";
                return 0;
            }

            SqlCommand cmd = sqlConn.CreateCommand();
            cmd.CommandText = string.Format("delete from Table_Xsxx where 学号='{0}'", xh);
            cmd.CommandType = System.Data.CommandType.Text;

            try
            {
                int iResult = cmd.ExecuteNonQuery();
                sError = "";
                return iResult;
            }
            catch (Exception ex)
            {
                sError = ex.Message;
                return 0;
                //throw;
            }
        }


    }
}
